import traceback

import psycopg2

try:
    from main import config_util
except:
    import config_util


def __execute_sql(sql="show server_version"):
    database = config_util.getConfig("postgresql", "database")
    user = config_util.getConfig("postgresql", "user")
    password = config_util.getConfig("postgresql", "password")
    host = config_util.getConfig("postgresql", "host")
    port = config_util.getConfig("postgresql", "port")
    conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    """:type : psycopg2.extensions.connection"""
    print("Opened database successfully")
    cur = conn.cursor()
    """:type : psycopg2.extensions.cursor"""
    cur.execute(sql)
    conn.commit()
    conn.close()
    return "success"


def __create_seq_sql(tablename, rowname):
    """
    生成创建序列的SQL语句
    :param tablename: 表名
    :param rowname: 字段名称
    :return: 创建序列的SQL语句
    """
    seq_name = "{tablename}_{rowname}_seq".format(tablename=tablename, rowname=rowname)
    sql = """CREATE SEQUENCE "{seq_name}" 
      INCREMENT 1
      MINVALUE  1
      MAXVALUE 2147483647
      START 1
      CACHE 1""".format(seq_name=seq_name)
    return sql


def __create_table_sql(table_name, rows_dict=None):
    """
    生成创建表的SQL语句
    :param table_name: 表名
    :param rows_dict: 字段字典
    :return: 创建标的sql
    """
    srid = config_util.getConfig("srid", "srid")
    if rows_dict is None:
        rows_dict = {}
    gid = "gid int4 NOT NULL DEFAULT nextval('{table_name}_gid_seq'::regclass),".format(table_name=table_name)
    geom = "geom geometry({type},{srid}),".format(type="MULTIPOLYGON",srid=srid)
    rows = ""
    for row in rows_dict:
        rows += "{row} {type},\n".format(row=row, type=__typeChange(rows_dict[row]))
    pkey = "CONSTRAINT {table_name}_pkey PRIMARY KEY (gid)".format(table_name=table_name)
    sql = """create table {table_name}(\n{gid}\n{rows}{geom}\n{pkey});""".format(table_name=table_name, gid=gid,
                                                                                 geom=geom,
                                                                                 pkey=pkey, rows=rows)
    return sql


def __insert_sql(table_name, rows_dict, value_list):
    """
    生成插入数据的SQL
    :param table_name: 表名
    :param rows_dict: 列名
    :param value_list: 值
    :return: 插入数据的SQL
    """
    srid = config_util.getConfig("srid", "srid")
    fields = ""
    for row in rows_dict:
        fields += row + ","
    sql = "insert into {table_name}({fields}geom) values".format(table_name=table_name, fields=fields)
    for i in range(len(value_list)):
    # for i in range(10):
        value = value_list[i]
        value_sql = "("
        for row in rows_dict:
            if value[row] is None:
                value_sql += "null,"
            else:
                if rows_dict[row] == "Integer":
                    value_sql += str(value[row]) + ","
                if rows_dict[row] == "String":
                    value_sql += "'{value}',".format(value=value[row])
        value_sql += "ST_GeomFromText('{geom}',{srid})".format(geom=value["geom"],srid=srid)
        value_sql += ")"
        sql += value_sql
        if i != len(value_list) - 1:
            sql += ","
    return sql


def __typeChange(type_name):
    if type_name == "Integer":
        return "int4"
    if type_name == "String":
        return "varchar(50)"


def import_geodata(table_name, rows_dict, value_list):
    database = config_util.getConfig("postgresql", "database")
    user = config_util.getConfig("postgresql", "user")
    password = config_util.getConfig("postgresql", "password")
    host = config_util.getConfig("postgresql", "host")
    port = config_util.getConfig("postgresql", "port")
    conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    """:type : psycopg2.extensions.connection"""
    print("Opened database successfully")
    cur = conn.cursor()
    print(type(cur))
    """:type : psycopg2.extensions.cursor"""
    create_seq_sql = __create_seq_sql(table_name, "gid")
    # print(create_seq_sql)
    create_table_sql = __create_table_sql(table_name, rows_dict)
    # print(create_table_sql)
    insert_sql = __insert_sql(table_name, rows_dict, value_list)
    # print(insert_sql)
    try:
        cur.execute(create_seq_sql)
        cur.execute(create_table_sql)
        cur.execute(insert_sql)
    except Exception as e:
        traceback.print_exc()
        conn.rollback()
        return False
    else:
        conn.commit()
        # conn.rollback()
        return True
    finally:
        conn.close


def main():
    table_name = "test"
    # create_seq_sql = __create_seq_sql(table_name, "gid")
    # print(create_seq_sql)
    # print(__execute_sql(create_seq_sql))

    rows_dict = {'Id': 'Integer', 'NAME': 'String'}
    # create_table_sql = __create_table_sql("test", rows_dict)
    # print(create_table_sql)
    # print(__execute_sql(create_table_sql))

    # 插入
    value_dict = {'Id': None,
                  'geom': 'POLYGON ((113.23503 6.32303,113.23453 6.31953,113.23203 6.31903,113.23003 6.32053,'
                          '113.23203 6.32203,113.23503 6.32303))',
                  'NAME': None}
    value_dict2 = {'Id': None, 'geom': 'POLYGON ((113.25003 6.34003,113.25003 6.33603,113.24703 6.33403,113.24403 '
                                       '6.33503,113.24553 6.33753,113.25003 6.34003))', 'NAME': None}

    value_list = [value_dict, value_dict2]
    insert_sql = __insert_sql(table_name, rows_dict, value_list)
    print(insert_sql)
    # import_geodata(table_name, rows_dict, value_list)


if __name__ == '__main__':
    main()
